﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace NG
{
    class ProjectDepartmentHandler:IProjectDepartmentHandler
    {
        private string tableName = "[dbo].[ProjectDepartments]";

        public void AddProjectDepartment(Project pro, SubDepartment dep)
        {
            DBConnect con = new DBConnect();
            string tbn = "[" + con.dbName + "]." + this.tableName;
            if (con.OpenConnection() == false)
            {
                throw new System.Exception("Couldn't connect to the Database");
            }

            string query = "INSERT INTO " + tbn + " (project,department,subDepartment) VALUES (" + pro.ID + "," + dep.DepCode + ","+ dep.SubDepCode + ");";


            try
            {
                SqlCommand cmd = new SqlCommand(query, con.connection);
                cmd.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            if (con.CloseConnection() == false)
            {
                throw new System.Exception("Couldn't disconnect from the Database");
            }
        }


        public SubDepartment subDepartmentOfGivenProject(Project pro)
        {
            DBConnect con = new DBConnect();
            string tbn = "[" + con.dbName + "]." + this.tableName;
            if (con.OpenConnection() == false)
            {
                throw new System.Exception("Couldn't connect to the Database");
            }
            string query = string.Format("SELECT * FROM {0} WHERE project = {1}", tbn, pro.ID);

            SqlCommand cmd = new SqlCommand(query, con.connection);
            SqlDataReader rdr = cmd.ExecuteReader();
            SubDepartment sd;
            if (rdr.HasRows)
            {
                rdr.Read();
                 sd = new SubDepartment(int.Parse(rdr["department"].ToString()), int.Parse(rdr["subDepartment"].ToString()), "");
            }
            else
            {
                sd = null; 
            }

            if (con.CloseConnection() == false)
            {
                throw new System.Exception("Couldn't disconnect from the Database");
            }
       
            return sd;


        }
    }
}
